/*
	select * FROM Cost_Code_Table ORDER BY COST_CODE
	SELECT * FROM Cost_Code_History_Table  ORDER BY COST_CODE

	SELECT * FROM LOT_TABLE_HISTORY
	SELECT * FROM LOT_TABLE
	SELECT * FROM PRODUCT
	SELECT DISTINCT PS_TYPE,PS_TYPE_NAME FROM PROCESS_STEP

	EXEC sp_cost_variance_report_proc '08/05/2007','07/17/2007',0
	--582
*/
ALTER PROC sp_cost_variance_report_proc

@FIRSTTIMESTAMP		VARCHAR(50),
@SECONDTIMESTAMP	VARCHAR(50),
@DEFAULT_PROMPT		VARCHAR(50)

AS
BEGIN

	DECLARE @TEMP_COUNT INT	

	DECLARE @FINAL_RESULTS TABLE
			([BASE PRODUCT] VARCHAR(50), [PART NUMBER] VARCHAR(50), [COST CODE] VARCHAR(20),
			 [FAB CURR COST] NUMERIC (18,4),[FAB PREV COST] NUMERIC (18,4),[FAB DELTA] NUMERIC (18,4),
			 [FO CURR COST] NUMERIC (18,4),[FO PREV COST] NUMERIC (18,4),[FO DELTA] NUMERIC (18,4),
			 [WBANK CURR COST] NUMERIC (18,4),[WBANK PREV COST] NUMERIC (18,4),[WBANK DELTA] NUMERIC (18,4),
			 [DBANK CURR COST] NUMERIC (18,4),[DBANK PREV COST] NUMERIC (18,4),[DBANK DELTA] NUMERIC (18,4),
			 [ASSY CURR COST] NUMERIC (18,4),[ASSY PREV COST] NUMERIC (18,4),[ASSY DELTA] NUMERIC (18,4),
			 [FPO CURR COST] NUMERIC (18,4),[FPO PREV COST] NUMERIC (18,4),[FPO DELTA] NUMERIC (18,4),
			 [FT CURR COST] NUMERIC (18,4),[FT PREV COST] NUMERIC (18,4),[FT DELTA] NUMERIC (18,4),
			 [T&R CURR COST] NUMERIC (18,4),[T&R PREV COST] NUMERIC (18,4),[T&R DELTA] NUMERIC (18,4),
			 [FG CURR COST] NUMERIC (18,4),[FG PREV COST] NUMERIC (18,4),[FG DELTA] NUMERIC (18,4) )
			  
	DECLARE @CURRENT_INVENTORY TABLE 
			( PROCESSSTEP_ID INT, WAFER_QTY INT,QTY_IN INT, PS_TYPE VARCHAR(50), --PS_TYPE_ID INT,
			  PART_NUMBER VARCHAR(50), BASE_PRODUCT VARCHAR(50),COST_CODE VARCHAR(20), 
			  CURRENT_COST NUMERIC (18,4), PREVOIUS_COST NUMERIC (18,4) )

	DECLARE @TEMP_RESULTS TABLE ( CC_ID INT, COST_CODE VARCHAR(50), QTR INT, YEAR INT, DIE_COST NUMERIC(18, 3), ASSM_COST NUMERIC(18, 3), TEST_COST NUMERIC(18, 3), FG_COST NUMERIC(18, 3), TR_COST NUMERIC(18, 3), WAFER_COST NUMERIC(18, 3), ASSY VARCHAR(50), BASE VARCHAR(50), PKG VARCHAR(50), PIN VARCHAR(50), PKG_DESC VARCHAR(100), TIMESTAMP DATETIME )
		
	DECLARE @START_DATE 	DATETIME
    DECLARE @END_DATE 		DATETIME

	DECLARE @START_DATE1 	DATETIME
    DECLARE @END_DATE1 		DATETIME

    DECLARE @TEMP_VARCHAR 	VARCHAR(20)
	    	    
   	SET @TEMP_VARCHAR = CONVERT(VARCHAR, @FIRSTTIMESTAMP, 101)+' 12:00AM'
    SET @START_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)
    SET @TEMP_VARCHAR = NULL
    SET @TEMP_VARCHAR = CONVERT(VARCHAR, @FIRSTTIMESTAMP, 101)+' 11:59PM' 
    SET @END_DATE = CONVERT(DATETIME, @TEMP_VARCHAR)

	set @TEMP_VARCHAR = null 

	SELECT TOP 1 @SECONDTIMESTAMP = CAST( TIMESTAMP AS CHAR(11)) FROM COST_CODE_HISTORY_TABLE WHERE CAST( TIMESTAMP AS CHAR(11)) > @END_DATE ORDER BY TIMESTAMP --DESC

	SELECT @TEMP_COUNT = COUNT(TIMESTAMP) FROM COST_CODE_HISTORY_TABLE WHERE CAST( TIMESTAMP AS CHAR(11)) > @END_DATE GROUP BY TIMESTAMP

	--PRINT ' 1@SECONDTIMESTAMP = ' + @SECONDTIMESTAMP + ' @TEMP_COUNT = ' + CONVERT( VARCHAR, ISNULL( @TEMP_COUNT, -11) ) 

	SET @TEMP_COUNT = ISNULL( @TEMP_COUNT, 0 )

	SET @TEMP_VARCHAR = CONVERT(VARCHAR, @SECONDTIMESTAMP, 101)+' 12:00AM'
	SET @START_DATE1 = CONVERT(DATETIME, @TEMP_VARCHAR)
	SET @TEMP_VARCHAR = NULL
	SET @TEMP_VARCHAR = CONVERT(VARCHAR, @SECONDTIMESTAMP, 101)+' 11:59PM' 
	SET @END_DATE1 = CONVERT(DATETIME, @TEMP_VARCHAR)
	
	--PRINT ' @SECONDTIMESTAMP = ' + @SECONDTIMESTAMP + ' @TEMP_COUNT = ' + CONVERT( VARCHAR, ISNULL( @TEMP_COUNT, -11) ) 

	IF( @TEMP_COUNT > 0 )
	BEGIN
		INSERT INTO @TEMP_RESULTS
		SELECT * FROM COST_CODE_HISTORY_TABLE WHERE QTR = DATEPART(Q, @SECONDTIMESTAMP) AND YEAR = DATEPART(YYYY, @SECONDTIMESTAMP) AND TIMESTAMP BETWEEN @START_DATE1 AND @END_DATE1 		
	END
	ELSE
	BEGIN
		INSERT INTO @TEMP_RESULTS( CC_ID , COST_CODE, QTR, YEAR, DIE_COST, ASSM_COST, TEST_COST, FG_COST, TR_COST, WAFER_COST, ASSY, BASE, PKG, PIN, PKG_DESC )
		SELECT * FROM COST_CODE_TABLE WHERE QTR = ( SELECT MAX(QTR) FROM  COST_CODE_TABLE ) AND YEAR = ( SELECT MAX(YEAR) FROM  COST_CODE_TABLE ) 
	END

	IF ( CONVERT(VARCHAR,@FIRSTTIMESTAMP,101) <> CONVERT(VARCHAR,CURRENT_TIMESTAMP,101) )
	BEGIN
		insert into @CURRENT_INVENTORY
		SELECT 
			LT.PROCESSSTEP_ID, SUM(WAFER_QTY_IN),SUM(QTY_IN),PS.PS_TYPE,P.PART_NUMBER,
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END ) AS BASE_PRODUCT,
			P.COST_CODE,
			(CASE 
				WHEN PS_TYPE = 'FG' THEN  isnull(CCHT.FG_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'TAPE & REEL' THEN  isnull(CCHT.TR_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'FT' OR PS_TYPE = 'FPO' THEN  isnull(CCHT.TEST_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'ASSEMBLY' THEN  isnull(CCHT.ASSM_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'DIE BANK' THEN  isnull(CCHT.DIE_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				ELSE isnull(CCHT.WAFER_COST,0.0)* ISNULL(SUM(WAFER_QTY_IN),0)
			END),
			(CASE 
				WHEN PS_TYPE = 'FG' THEN  isnull(CCHT1.FG_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'TAPE & REEL' THEN  isnull(CCHT1.TR_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'FT' OR PS_TYPE = 'FPO' THEN  isnull(CCHT1.TEST_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'ASSEMBLY' THEN  isnull(CCHT1.ASSM_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'DIE BANK' THEN  isnull(CCHT1.DIE_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				ELSE isnull(CCHT1.WAFER_COST,0.0)* ISNULL(SUM(WAFER_QTY_IN),0)
			END)
		FROM 
			((LOT_TABLE_HISTORY LT INNER JOIN PROCESS_STEP PS 
				ON LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID)
			INNER JOIN PRODUCT P ON PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID)	
			LEFT OUTER JOIN Cost_Code_History_Table CCHT ON P.COST_CODE = CCHT.COST_CODE and (CCHT.QTR = DATEPART(Q,@FIRSTTIMESTAMP) AND CCHT.YEAR = DATEPART(YYYY,@FIRSTTIMESTAMP) ) and ccht.timestamp between @START_DATE AND @END_DATE 
			LEFT OUTER JOIN @TEMP_RESULTS CCHT1 ON P.COST_CODE = CCHT1.COST_CODE --and ( CCHT1.QTR = DATEPART(Q,@SECONDTIMESTAMP) AND CCHT1.YEAR = DATEPART(YYYY,@SECONDTIMESTAMP) ) and ccht1.timestamp between @START_DATE1 AND @END_DATE1
			WHERE 	
				LOT_CURRENT_STATUS <> 'CLOSE' AND LT.TIMESTAMP BETWEEN @START_DATE AND @END_DATE
				--AND P_PARENT_PRODUCT_ID = 5 AND P.COST_CODE = 'BQ56'
			GROUP BY LT.PROCESSSTEP_ID,PS.PS_TYPE,P.PART_NUMBER,P.P_PARENT_PRODUCT_ID,P.PRODUCT_ID,P.COST_CODE,CCHT.FG_COST,CCHT.TR_COST,CCHT.TEST_COST,CCHT.ASSM_COST,CCHT.DIE_COST,CCHT.WAFER_COST
				,CCHT1.FG_COST,CCHT1.TR_COST,CCHT1.TEST_COST,CCHT1.ASSM_COST,CCHT1.DIE_COST,CCHT1.WAFER_COST

		--SELECT * FROM @CURRENT_INVENTORY

	END
	ELSE
	BEGIN
		insert into @CURRENT_INVENTORY
		SELECT 
			LT.PROCESSSTEP_ID, SUM(WAFER_QTY_IN),SUM(QTY_IN),PS.PS_TYPE,P.PART_NUMBER,
			(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
				CASE 
					WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
					WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
				END ) AS BASE_PRODUCT,
			P.COST_CODE,
			(CASE 
				WHEN PS_TYPE = 'FG' THEN  isnull(CCHT.FG_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'TAPE & REEL' THEN  isnull(CCHT.TR_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'FT' OR PS_TYPE = 'FPO' THEN  isnull(CCHT.TEST_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'ASSEMBLY' THEN  isnull(CCHT.ASSM_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'DIE BANK' THEN  isnull(CCHT.DIE_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				ELSE isnull(CCHT.WAFER_COST,0.0)* ISNULL(SUM(WAFER_QTY_IN),0)
			END),
			(CASE 
				WHEN PS_TYPE = 'FG' THEN  isnull(CCHT1.FG_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'TAPE & REEL' THEN  isnull(CCHT1.TR_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'FT' OR PS_TYPE = 'FPO' THEN  isnull(CCHT1.TEST_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'ASSEMBLY' THEN  isnull(CCHT1.ASSM_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				WHEN PS_TYPE = 'DIE BANK' THEN  isnull(CCHT1.DIE_COST,0.0)* ISNULL(SUM(QTY_IN),0)
				ELSE isnull(CCHT1.WAFER_COST,0.0)* ISNULL(SUM(WAFER_QTY_IN),0)
			END)
		FROM 
			((LOT_TABLE LT INNER JOIN PROCESS_STEP PS 
				ON LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID)
			INNER JOIN PRODUCT P ON PS.PROCESSSTEP_ID = P.PROCESSSTEP_ID)	
			LEFT OUTER JOIN Cost_Code_History_Table CCHT ON P.COST_CODE = CCHT.COST_CODE and (CCHT.QTR = DATEPART(Q,@FIRSTTIMESTAMP) AND CCHT.YEAR = DATEPART(YYYY,@FIRSTTIMESTAMP) ) and ccht.timestamp between @START_DATE AND @END_DATE 
			LEFT OUTER JOIN @TEMP_RESULTS CCHT1 ON P.COST_CODE = CCHT1.COST_CODE --and ( CCHT1.QTR = DATEPART(Q,@SECONDTIMESTAMP) AND CCHT1.YEAR = DATEPART(YYYY,@SECONDTIMESTAMP) ) and ccht1.timestamp between @START_DATE1 AND @END_DATE1
		WHERE 	
			LOT_CURRENT_STATUS <> 'CLOSE'
		GROUP BY LT.PROCESSSTEP_ID,PS.PS_TYPE,P.PART_NUMBER,P.P_PARENT_PRODUCT_ID,P.PRODUCT_ID,P.COST_CODE,CCHT.FG_COST,CCHT.TR_COST,CCHT.TEST_COST,CCHT.ASSM_COST,CCHT.DIE_COST,CCHT.WAFER_COST
			,CCHT1.FG_COST,CCHT1.TR_COST,CCHT1.TEST_COST,CCHT1.ASSM_COST,CCHT1.DIE_COST,CCHT1.WAFER_COST
	END

	--SELECT * FROM @CURRENT_INVENTORY
	INSERT INTO @FINAL_RESULTS
	SELECT 
		BASE_PRODUCT AS [BASE PRODUCT],PART_NUMBER AS [PART NUMBER],COST_CODE AS [COST CODE],
		--FAB
		(SELECT CI00.CURRENT_COST FROM @CURRENT_INVENTORY CI00 WHERE CI00.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI00.PS_TYPE = 'FAB') AS [FAB CURRENT COST],
		(SELECT CI01.PREVOIUS_COST FROM @CURRENT_INVENTORY CI01 WHERE CI01.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI01.PS_TYPE = 'FAB') AS [FAB PREVIOUS COST],

		( (SELECT CI02.CURRENT_COST FROM @CURRENT_INVENTORY CI02 WHERE CI02.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI02.PS_TYPE = 'FAB')-
		(SELECT CI03.PREVOIUS_COST FROM @CURRENT_INVENTORY CI03 WHERE CI03.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI03.PS_TYPE = 'FAB') ) AS [FAB DELTA],
		--FAB OPTION
		(SELECT CI10.CURRENT_COST FROM @CURRENT_INVENTORY CI10 WHERE CI10.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI10.PS_TYPE = 'FAB OPTION') AS [FAB OPTION CURRENT COST],
		(SELECT CI11.PREVOIUS_COST FROM @CURRENT_INVENTORY CI11 WHERE CI11.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI11.PS_TYPE = 'FAB OPTION') AS [FAB OPTION PREVIOUS COST],

		( (SELECT CI12.CURRENT_COST FROM @CURRENT_INVENTORY CI12 WHERE CI12.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI12.PS_TYPE = 'FAB OPTION')-
		(SELECT CI13.PREVOIUS_COST FROM @CURRENT_INVENTORY CI13 WHERE CI13.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI13.PS_TYPE = 'FAB OPTION') ) AS [FAB OPTION DELTA],
		--WAFER BANK
		(SELECT CI20.CURRENT_COST FROM @CURRENT_INVENTORY CI20 WHERE CI20.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI20.PS_TYPE = 'WAFER BANK') AS [WAFER BANK CURRENT COST],
		(SELECT CI21.PREVOIUS_COST FROM @CURRENT_INVENTORY CI21 WHERE CI21.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI21.PS_TYPE = 'WAFER BANK') AS [WAFER BANK PREVIOUS COST],

		( (SELECT CI22.CURRENT_COST FROM @CURRENT_INVENTORY CI22 WHERE CI22.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI22.PS_TYPE = 'WAFER BANK')-
		(SELECT CI23.PREVOIUS_COST FROM @CURRENT_INVENTORY CI23 WHERE CI23.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI23.PS_TYPE = 'WAFER BANK') ) AS [WAFER BANK DELTA],
		--DIE BANK
		(SELECT CI30.CURRENT_COST FROM @CURRENT_INVENTORY CI30 WHERE CI30.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI30.PS_TYPE = 'DIE BANK') AS [DIE BANK CURRENT COST],
		(SELECT CI31.PREVOIUS_COST FROM @CURRENT_INVENTORY CI31 WHERE CI31.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI31.PS_TYPE = 'DIE BANK') AS [DIE BANK PREVIOUS COST],

		( (SELECT CI32.CURRENT_COST FROM @CURRENT_INVENTORY CI32 WHERE CI32.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI32.PS_TYPE = 'DIE BANK')-
		(SELECT CI33.PREVOIUS_COST FROM @CURRENT_INVENTORY CI33 WHERE CI33.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI33.PS_TYPE = 'DIE BANK') ) AS [DIE BANK DELTA],
		--ASSEMBLY
		(SELECT CI40.CURRENT_COST FROM @CURRENT_INVENTORY CI40 WHERE CI40.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI40.PS_TYPE = 'ASSEMBLY') AS [ASSEMBLY CURRENT COST],
		(SELECT CI41.PREVOIUS_COST FROM @CURRENT_INVENTORY CI41 WHERE CI41.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI41.PS_TYPE = 'ASSEMBLY') AS [ASSEMBLY PREVIOUS COST],

		( (SELECT CI42.CURRENT_COST FROM @CURRENT_INVENTORY CI42 WHERE CI42.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI42.PS_TYPE = 'ASSEMBLY')-
		(SELECT CI43.PREVOIUS_COST FROM @CURRENT_INVENTORY CI43 WHERE CI43.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI43.PS_TYPE = 'ASSEMBLY') ) AS [ASSEMBLY DELTA],
		--FP0
		(SELECT CI50.CURRENT_COST FROM @CURRENT_INVENTORY CI50 WHERE CI50.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI50.PS_TYPE = 'FPO') AS [FPO CURRENT COST],
		(SELECT CI51.PREVOIUS_COST FROM @CURRENT_INVENTORY CI51 WHERE CI51.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI51.PS_TYPE = 'FPO') AS [FPO PREVIOUS COST],

		( (SELECT CI52.CURRENT_COST FROM @CURRENT_INVENTORY CI52 WHERE CI52.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI52.PS_TYPE = 'FPO')-
		(SELECT CI53.PREVOIUS_COST FROM @CURRENT_INVENTORY CI53 WHERE CI53.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI53.PS_TYPE = 'FPO') ) AS [FPO DELTA],
		--FT
		(SELECT CI60.CURRENT_COST FROM @CURRENT_INVENTORY CI60 WHERE CI60.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI60.PS_TYPE = 'FT') AS [FT CURRENT COST],
		(SELECT CI61.PREVOIUS_COST FROM @CURRENT_INVENTORY CI61 WHERE CI61.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI61.PS_TYPE = 'FT') AS [FT PREVIOUS COST],

		( (SELECT CI62.CURRENT_COST FROM @CURRENT_INVENTORY CI62 WHERE CI62.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI62.PS_TYPE = 'FT')-
		(SELECT CI63.PREVOIUS_COST FROM @CURRENT_INVENTORY CI63 WHERE CI63.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI63.PS_TYPE = 'FT') ) AS [FT DELTA],
		--TAPE & REEL
		(SELECT CI70.CURRENT_COST FROM @CURRENT_INVENTORY CI70 WHERE CI70.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI70.PS_TYPE = 'TAPE & REEL') AS [TAPE & REEL CURRENT COST],
		(SELECT CI71.PREVOIUS_COST FROM @CURRENT_INVENTORY CI71 WHERE CI71.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI71.PS_TYPE = 'TAPE & REEL') AS [TAPE & REEL PREVIOUS COST],

		( (SELECT CI72.CURRENT_COST FROM @CURRENT_INVENTORY CI72 WHERE CI72.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI72.PS_TYPE = 'TAPE & REEL')-
		(SELECT CI73.PREVOIUS_COST FROM @CURRENT_INVENTORY CI73 WHERE CI73.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI73.PS_TYPE = 'TAPE & REEL') ) AS [TAPE & REEL DELTA],
		--FG
		(SELECT CI80.CURRENT_COST FROM @CURRENT_INVENTORY CI80 WHERE CI80.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI80.PS_TYPE = 'FG') AS [FG CURRENT COST],
		(SELECT CI81.PREVOIUS_COST FROM @CURRENT_INVENTORY CI81 WHERE CI81.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI81.PS_TYPE = 'FG') AS [FG PREVIOUS COST],

		( (SELECT CI82.CURRENT_COST FROM @CURRENT_INVENTORY CI82 WHERE CI82.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI82.PS_TYPE = 'FG')-
		(SELECT CI83.PREVOIUS_COST FROM @CURRENT_INVENTORY CI83 WHERE CI83.PROCESSSTEP_ID = CI.PROCESSSTEP_ID AND CI83.PS_TYPE = 'FG') ) AS [FG DELTA]
	FROM 
		@CURRENT_INVENTORY CI
	--GROUP BY CI.BASE_PRODUCT,CI.PART_NUMBER,CI.COST_CODE,CI.PROCESSSTEP_ID
		
	ORDER BY 
		BASE_PRODUCT,PART_NUMBER

	SELECT 
		[BASE PRODUCT],[PART NUMBER],[COST CODE],
		SUM([FAB CURR COST]) [FAB CURR COST],SUM([FAB PREV COST]) [FAB PREV COST],SUM([FAB DELTA]) [FAB DELTA],
		SUM([FO CURR COST]) [FO CURR COST],SUM([FO PREV COST]) [FO PREV COST],SUM([FO DELTA]) [FO DELTA],
		SUM([WBANK CURR COST]) [WBANK CURR COST],SUM([WBANK PREV COST]) [WBANK PREV COST],SUM([WBANK DELTA]) [WBANK DELTA],
		SUM([DBANK CURR COST]) [DBANK CURR COST],SUM([DBANK PREV COST]) [DBANK PREV COST],SUM([DBANK DELTA]) [DBANK DELTA],
		SUM([ASSY CURR COST]) [ASSY CURR COST],SUM([ASSY PREV COST]) [ASSY PREV COST],SUM([ASSY DELTA]) [ASSY DELTA],
		SUM([FPO CURR COST]) [FPO CURR COST],SUM([FPO PREV COST]) [FPO PREV COST],SUM([FPO DELTA]) [FPO DELTA],
		SUM([FT CURR COST]) [FT CURR COST],SUM([FT PREV COST]) [FT PREV COST],SUM([FT DELTA]) [FT DELTA],
		SUM([FG CURR COST]) [FG CURR COST],SUM([FG PREV COST]) [FG PREV COST],SUM([FG DELTA]) [FG DELTA],
		SUM([T&R CURR COST]) [T&R CURR COST],SUM([T&R PREV COST]) [T&R PREV COST],SUM([T&R DELTA]) [T&R DELTA]
		
	FROM 
		@FINAL_RESULTS
	WHERE 
		[COST CODE] IS NOT NULL

	GROUP BY
		[BASE PRODUCT],[PART NUMBER],[COST CODE]
	ORDER BY
		[BASE PRODUCT],[PART NUMBER]
		
END 
